import json
import pandas as pd


def load_json(file_path):
    """Load JSON data from a file."""
    with open(file_path, 'r', encoding='utf-8') as f:
        return json.load(f)


def calculate_position(x1, y1, row_height=50, col_width=100):
    """
    Calculate the row and column index based on x1, y1 coordinates.
    Adjust row_height and col_width according to your template.
    """
    row = y1 // row_height
    col = x1 // col_width
    return row, col


def organize_data(data, row_height=50, col_width=100):
    """
    Organize the data into a 2D list based on position coordinates.
    Adjust row_height and col_width according to your template.
    """
    # 初始化一个足够大的二维数组来存储表格内容
    # 假设最大行列数为100，可根据实际情况调整
    max_row = max_col = 100
    table = [["" for _ in range(max_col)] for _ in range(max_row)]

    for item in data:
        x1, y1, _, _, _, _, _, _ = item["position"]  # 只需要左上角坐标
        row, col = calculate_position(x1, y1, row_height, col_width)
        table[row][col] = item["text"]

    return table


def export_to_excel(table, output_file):
    """Export the organized table data to an Excel file."""
    df = pd.DataFrame(table)
    # 清除尾随空白列
    df = df.dropna(axis=1, how='all')
    # 清除尾随空白行
    df = df.dropna(axis=0, how='all')
    df.to_excel(output_file, index=False, header=False)


# 主程序逻辑
json_file_path = 'C:/Users/zhuka/Downloads/00_rec.json'  # 替换为您的JSON文件路径
output_excel_file = 'C:/Users/zhuka/Downloads/00_rec.xlsx'  # 输出的Excel文件名

# 加载数据
data = load_json(json_file_path)

# 组织数据
organized_table = organize_data(data)

# 导出到Excel
export_to_excel(organized_table, output_excel_file)

print(f"Data has been successfully exported to {output_excel_file}")